package com.ibm.ems.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;



import com.ibm.ems.dto.TopicBean;


//Class DAO to access database

public class TopicsDao {

	private Connection conn;

	public TopicsDao(Connection conn) {
		this.conn = conn;
	}
	

//--------------------------------------------------------------------------------
//--------------------------------------------------------------------------------
//To create a Topic
public void TopicsDaoCreate(TopicBean t1)
{
	

	PreparedStatement pstmt = null;
	
	
	
	


//t1.setTopic_code(request.getParameter("TopicCode"));
//t1.setCategory(request.getParameter("Category"));

try{



pstmt=conn.prepareStatement("insert into topic values(?,?,?,?,?)");	

pstmt.setString(1,t1.getTopicId());
pstmt.setString(2,t1.getCategory());
pstmt.setString(3,t1.getTopicName());
pstmt.setString(4,t1.getDescription());
pstmt.setString(5,"open");
pstmt.executeQuery();



}catch(SQLException e){}
catch(Exception e)
{}


}
//--------------------------------------------------------------------------------
//--------------------------------------------------------------------------------
//To Create Topics Home Page
public void TopicsDaoInit(ArrayList<TopicBean> l) 		
{
	
	PreparedStatement pstmt = null;
	ResultSet rs = null;

	try {
		
			//ArrayList<String> l1=new ArrayList<String>();
		//ArrayList<String> l2=new ArrayList<String>();
		//ArrayList<String> l3=new ArrayList<String>();
		
		
		
		try {
			pstmt = conn.prepareStatement("select * from topic");
			int count=0;
		TopicBean f=new TopicBean();
			rs = pstmt.executeQuery();
			
			while (rs.next())
			{
				f.setCategory(rs.getString("CATEGORY"));
				f.setTopicId(rs.getString("TOPIC_ID"));											
				f.setDescription(rs.getString("DESCRIPTION"));
				f.setTopicName(rs.getString("TOPIC_NAME"));
				f.setStatus(rs.getString("STATUS"));
				l.add(count, f);
				
				f=new TopicBean();
				count++;
						}
				
			
				
			
			
		} 
		
		catch (SQLException ex)
		{
			throw ex;
		}
		
		finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
			} catch (Exception ex1){
				
			}
		
		
		}
		
		
		
	
	
	
	} 
	
	
	catch (SQLException ex) {
		// TODO Auto-generated catch block
		ex.printStackTrace();
	}
	




}
	
	
//--------------------------------------------------------------------------------
//--------------------------------------------------------------------------------
//To Recieve data from database
public void modify(TopicBean t)
{
	
	PreparedStatement pstmt = null;
	ResultSet rs;
try{
	pstmt=conn.prepareStatement("select topic_id,topic_name,category,DESCRIPTION from topic where topic_id =? ");
	pstmt.setString(1,t.getTopicId());
	
	rs = pstmt.executeQuery();
	
	rs.next();
	t.setCategory(rs.getString("CATEGORY"));
	t.setDescription(rs.getString("DESCRIPTION"));
	t.setTopicId(rs.getString("TOPIC_ID"));
	t.setTopicName(rs.getString("TOPIC_NAME"));
	t.setStatus(rs.getString("TOPIC_STATUS"));
	
	
	conn.close();
		
	
}catch(SQLException e)
{}
	
	


}
//--------------------------------------------------------------------------------
//--------------------------------------------------------------------------------
//To modify in database
public void modifydatabase(TopicBean t)
{
	PreparedStatement pstmt = null;

	try{
		pstmt=conn.prepareStatement("update topic set topic_name=?,CATEGORY=?,description=? where topic_id=? ");
		
		pstmt.setString(1,t.getTopicName());
		pstmt.setString(2,t.getCategory());
		pstmt.setString(3,t.getDescription());
		pstmt.setString(4,t.getTopicId());
		
		
		pstmt.executeQuery();
			
	}catch(SQLException e)
	{}finally {
		try {
			
			if (pstmt != null)
				pstmt.close();
		} catch (Exception ex1){
			
		}
	}

}
public void modifyStatus(TopicBean p)
{
	PreparedStatement pstmt = null;

	try{
		pstmt=conn.prepareStatement("update topic set TOPIC_STATUS=? where topic_id=? ");
		
		pstmt.setString(1, p.getStatus());
		pstmt.setString(2,p.getTopicId());
		
		pstmt.executeQuery();
			
	}catch(SQLException e)
	{}finally {
		try {
			
			if (pstmt != null)
				pstmt.close();
		} catch (Exception ex1){
			
		}
	}
	
}


public int checkpoll(int count) 
{

	PreparedStatement pstmt = null;
	ResultSet rs = null;

	try {
		
			//ArrayList<String> l1=new ArrayList<String>();
		//ArrayList<String> l2=new ArrayList<String>();
		//ArrayList<String> l3=new ArrayList<String>();
		
	
	try {
		pstmt = conn.prepareStatement("select LOWER(POLL) from POLLING");
		
	
		rs = pstmt.executeQuery();
		String a;
		while (rs.next())
		{
			
			a=rs.getString("POLL");
		if(a=="y")
			{count++;}
					}
			
		
			
		
		
	} 
	catch (SQLException ex)
	{
		throw ex;
	} 
	} catch (SQLException ex) {
	// TODO Auto-generated catch block
	ex.printStackTrace();
}




	
	
	
	return count;
}
	
	

}
